import pymysql  # mysql
import pymssql  # sqlserver
import cx_Oracle  # oracle


class MyDB(object):
    '''连接mysql、sqlserver、oracle等数据库'''

    def __init__(self, classify, host, port, user, password, database, connect=None):
        self.classify = classify.lower().replace(' ', '')
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.connect = connect
        self.set_connect()

    def set_connect(self):
        '''建立数据连接'''
        if self.classify == 'mysql':  # 建立mysql连接
            self.connect = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password,
                                           database=self.database)
        elif self.classify == 'sqlserver':  # 建立sqlserver连接
            self.connect = pymssql.connect(self.host, self.user, self.password, self.database)
        elif self.classify == 'oracle':  # 建立oracle连接
            self.connect = cx_Oracle.connect(
                '{user}/{pwd}@{host}:{port}/{database}'.format(user=self.user, pwd=self.password, host=self.host,
                                                               port=self.port,
                                                               database=self.database))
        else:
            self.connect = None
        return self.connect

    def get_field_name(self, sql):
        '''获取查询字段名'''
        # 创建游标
        cur = self.connect.cursor()
        cur.execute(sql)
        field_names = cur.description
        result = [i[0] for i in field_names]
        return result

    def get_dict_datas(self, sql, count=None):
        '''获取字典形式数据'''
        field_names = self.get_field_name(sql)
        cur = self.connect.cursor()
        cur.execute(sql)
        if count:
            datas = cur.fetchmany(count)
        else:
            datas = cur.fetchall()
        # 生成dict数据
        result = [{field_name: data[index] for index, field_name in enumerate(field_names)} for data in datas]
        return result

    def get_list_datas(self, sql, count=None):
        '''获取二维数组形式数据'''
        cur = self.connect.cursor()
        cur.execute(sql)
        if count:
            datas = cur.fetchmany(count)
        else:
            datas = cur.fetchall()
        result = [[i for i in data] for data in datas]
        return result

    def get_all_tables(self):
        '''获取当前连接下所有表名'''
        if self.classify == 'mysql':
            sql = "select table_name from information_schema.tables where table_schema='{database}'".format(
                database=self.database)
        elif self.classify == 'sqlserver':
            sql = "select name from sysobjects where xtype='U'"
        elif self.classify == 'oracle':
            sql = "select TABLE_NAME from all_tables WHERE owner='{owner}'".format(owner=self.user)
        else:
            sql = ''
        cur = self.connect.cursor()
        cur.execute(sql)
        tables = cur.fetchall()
        result = [i[0] for i in tables]
        return result

    def close(self):
        '''关闭数据库连接'''
        self.connect.close()


if __name__ == '__main__':
    # mysql测试
    # conn = MyDB('mysql', '127.0.0.1', 3306, 'root', '123456', 'index')
    # field_list = conn.get_field_name("select * from app_cube_echart")
    # print(field_list)
    # datas_dict = conn.get_dict_datas("select * from app_cube_echart")
    # print(datas_dict)
    # datas_list = conn.get_list_datas("select * from app_cube_echart")
    # print(datas_list)
    # tables = conn.get_all_tables()
    # print(tables)


    # sqlserver测试
    conn = MyDB('sqlserver', '192.168.0.105', 1433, 'sa', '123456', 'DaXingTest')
    field_list = conn.get_field_name("select [test1], [test2], [test3], [x_axis] from drawing_test1")
    print(field_list)
    datas_dict = conn.get_dict_datas("select [test1], [test2], [test3], [x_axis] from drawing_test1")
    print(datas_dict)
    datas_list = conn.get_list_datas("select [test1], [test2], [test3], [x_axis] from drawing_test1")
    print(datas_list)
    tables = conn.get_all_tables()
    print(tables)


    # oracle测试
    # conn = MyDB('oracle', '127.0.0.1', 1521, 'C##ZJS', '123456', 'orcl')
    # field_list = conn.get_field_name('select * from "test"')
    # print(field_list)
    # datas_dict = conn.get_dict_datas('select * from "test"', 1)
    # print(datas_dict)
    # datas_list = conn.get_list_datas('select * from "test"', 1)
    # print(datas_list)
    # tables = conn.get_all_tables()
    # print(tables)
    # conn.close()
